﻿#region

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using log4net;

#endregion

namespace Chowpoo.Sql
{
    /// <summary>
    ///   SqlHelper 提供一次性开闭数据库访问请求的静态方法。
    /// </summary>
    public class SqlHelper : IDisposable
    {
        /// <summary>
        ///   默认的数据库连接名称
        /// </summary>
        public const string ConnStringName = "default";

        private readonly ILog _log;
        private bool _keepOpen;

        /// <summary>
        ///   根据连接名称创建SqlConnection
        /// </summary>
        /// <param name = "connectionStringName">连接名称（注意：不是连接字符串）</param>
        public SqlHelper(string connectionStringName)
            : this(connectionStringName, null)
        {
        }

        /// <summary>
        ///   根据连接名称创建SqlConnection
        /// </summary>
        /// <param name = "connectionStringName">连接名称（注意：不是连接字符串）</param>
        /// <param name = "configuration">System.Configuration对象</param>
        public SqlHelper(string connectionStringName, Configuration configuration)
        {
            _log = LogManager.GetLogger(GetType());
            ConnectionStringSettings connectionSettings;
            if (configuration != null && configuration.ConnectionStrings != null && configuration.ConnectionStrings.ConnectionStrings != null)
            {
                connectionSettings = configuration.ConnectionStrings.ConnectionStrings[connectionStringName];
            }
            else connectionSettings = ConfigurationManager.ConnectionStrings[connectionStringName];

            if (connectionSettings != null)
            {
                Connection = new SqlConnection(connectionSettings.ConnectionString);
                return;
            }
            _log.Error("未能在配置文件中找到名称为[" + connectionStringName + "]的项");
        }

        /// <summary>
        ///   使用默认连接名称"default"创建SqlConnection
        /// </summary>
        [Obsolete("请使用以连接名称作为参数的构造函数类型。多个部署于相同虚拟应用均使用默认构造函数可能导致冲突。")]
        public SqlHelper()
            : this(ConnStringName)
        {
        }

        /// <summary>
        ///   当前数据库连接
        /// </summary>
        public SqlConnection Connection { get; private set; }

        /// <summary>
        ///   默认为False，执行完方法后自动关闭连接
        ///   设置时为true时打开连接；false时关闭连接
        /// </summary>
        public bool KeepOpen
        {
            get { return _keepOpen; }
            set
            {
                if (Connection == null)
                    return;

                _keepOpen = value;
                if (value && Connection.State == ConnectionState.Closed)
                    Connection.Open();
                else if (!value && Connection.State == ConnectionState.Open)
                    Connection.Close();
            }
        }

        #region 过期方法

        /// <summary>
        ///   返回一个SqlDataReader实例，配置为空时返回Null
        /// </summary>
        /// <param name = "cmd">查询命令</param>
        /// <returns></returns>
        [Obsolete("临时屏蔽链接时会抛出异常，建议改用CreateReader方法")]
        public SqlDataReader ExecuteReader(SqlCommand cmd)
        {
            if (Connection == null)
                return null;

            cmd.Connection = Connection;
            if (Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            if (_keepOpen)
                return cmd.ExecuteReader();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        ///   返回一个SqlDataReader实例，配置为空时返回Null
        /// </summary>
        /// <param name = "commandText">查询命令</param>
        /// <returns></returns>
        [Obsolete("临时屏蔽链接时会抛出异常，建议改用CreateReader方法")]
        public SqlDataReader ExecuteReader(string commandText)
        {
            return CreateReader(new SqlCommand(commandText)) as SqlDataReader;
        }

        #endregion

        #region 核心方法

        /// <summary>
        ///   返回一个DbDataReader实例。当链接配置为空时返回一个DummyDBReader
        /// </summary>
        public DbDataReader CreateReader(SqlCommand cmd)
        {
            if (Connection == null)
                return DummyDataReader.Instance;
            cmd.Connection = Connection;
            if (Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            try
            {
                return _keepOpen ? cmd.ExecuteReader() : cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException e)
            {
                if (e.Number == -2)
                    throw new QueryTimeoutException();
                throw;
            }
        }

        /// <summary>
        ///   ExcuteScalar()方法
        /// </summary>
        /// <param name = "cmd">查询命令</param>
        /// <returns></returns>
        public object ExecuteScalar(SqlCommand cmd)
        {
            if (Connection == null)
                return null;

            object o;
            cmd.Connection = Connection;
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    cmd.Connection.Open();
                o = cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                if (e.Number == -2)
                    throw new QueryTimeoutException();
                throw;
            }
            finally
            {
                if (!_keepOpen)
                    Connection.Close();
            }
            return o;
        }

        /// <summary>
        ///   ExecuteNonQuery()方法
        /// </summary>
        /// <param name = "cmd">查询命令</param>
        /// <returns></returns>
        public int ExecuteNonQuery(SqlCommand cmd)
        {
            if (Connection == null)
                return -1;

            cmd.Connection = Connection;
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                if (e.Number == -2)
                    throw new QueryTimeoutException();
                throw;
            }
            finally
            {
                if (!_keepOpen)
                    Connection.Close();
            }
        }


        /// <summary>
        ///   返回一个保存查询结果的IDictionary
        /// </summary>
        /// <param name = "cmd">查询命令</param>
        /// <returns></returns>
        public IDictionary ExecuteRow(SqlCommand cmd)
        {
            if (Connection == null)
                return null;

            IDictionary row;
            cmd.Connection = Connection;
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    cmd.Connection.Open();
                using (var dr = cmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (dr != null && dr.Read())
                    {
                        if (dr.FieldCount < 8)
                            row = new HybridDictionary(dr.FieldCount);
                        else
                            row = new Dictionary<string, object>(dr.FieldCount);
                        for (var i = 0; i < dr.FieldCount; i++)
                            row.Add(dr.GetName(i), dr.GetValue(i));
                        return row;
                    }
                    return null;
                }
            }
            catch (SqlException e)
            {
                if (e.Number == -2)
                    throw new QueryTimeoutException();
                throw;
            }
            finally
            {
                if (!_keepOpen)
                    Connection.Close();
            }
        }

        #endregion

        #region 重载方法

        /// <summary>
        ///   Descriptiont:返回一个DbDataReader实例。当链接配置为空时返回一个DummyDBReader
        ///   Author:余观成
        ///   Date:2008/7/3
        /// </summary>
        /// <param name = "storedProcedure">存储过程</param>
        /// <param name = "parameters">参数数组</param>
        /// <returns></returns>
        public DbDataReader CreateReader(string storedProcedure, params SqlParameter[] parameters)
        {
            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection };
            if (parameters != null)
            {
                for (var i = 0; i < parameters.Length; i++)
                    cmd.Parameters.Add(parameters[i]);
            }
            return CreateReader(cmd);
        }

        /// <summary>
        ///   返回一个DbDataReader实例。当链接配置为空时返回一个DummyDBReader
        /// </summary>
        public DbDataReader CreateReader(string commandText)
        {
            return CreateReader(new SqlCommand(commandText));
        }

        /// <summary>
        ///   Description:返回查询第一行第一列的值
        ///   Author:余观成
        ///   Date:2008/7/3
        /// </summary>
        /// <param name = "storedProcedure">存储过程</param>
        /// <param name = "parameters">参数数组</param>
        /// <returns></returns>
        public object ExecuteScalar(string storedProcedure, params SqlParameter[] parameters)
        {
            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection };
            if (parameters != null)
            {
                for (var i = 0; i < parameters.Length; i++)
                    cmd.Parameters.Add(parameters[i]);
            }
            return ExecuteScalar(cmd);
        }

        /// <summary>
        ///   ExcuteScalar()方法
        /// </summary>
        /// <param name = "commandText">SQL命令行</param>
        /// <returns></returns>
        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(new SqlCommand(commandText));
        }

        /// <summary>
        ///   ExecuteNonQuery()方法
        /// </summary>
        /// <param name = "commandText">SQL命令行</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(new SqlCommand(commandText));
        }

        /// <summary>
        ///   Descriptiont:执行一个存储过程，后带参数
        ///   Author:余观成
        ///   Date:2008/7/3
        /// </summary>
        /// <param name = "storedProcedure">存储过程</param>
        /// <param name = "parameters">参数数组</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQuery(string storedProcedure, params SqlParameter[] parameters)
        {
            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection };
            if (parameters != null)
            {
                for (var i = 0; i < parameters.Length; i++)
                    cmd.Parameters.Add(parameters[i]);
            }
            return ExecuteNonQuery(cmd);
        }

        /// <summary>
        ///   返回一个保存查询结果的IDictionary
        /// </summary>
        /// <param name = "commandText">SQL命令行</param>
        /// <returns></returns>
        public IDictionary ExecuteRow(string commandText)
        {
            return ExecuteRow(new SqlCommand(commandText));
        }


        /// <summary>
        ///   Descriptiont:返回一个保存查询结果的IDictionary
        ///   Author:余观成
        ///   Date:2008/7/3
        /// </summary>
        /// <param name = "storedProcedure">存储过程</param>
        /// <param name = "parameters">参数数组</param>
        /// <returns></returns>
        public IDictionary ExecuteRow(string storedProcedure, params SqlParameter[] parameters)
        {
            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection };
            if (parameters != null)
            {
                for (var i = 0; i < parameters.Length; i++)
                    cmd.Parameters.Add(parameters[i]);
            }
            return ExecuteRow(cmd);
        }

        #endregion

        #region 屏蔽的方法

        /// <summary>
        ///   将查询的第一个结果集作为DataTable返回
        ///   注意：性能较低的实现方式。不建议使用
        /// </summary>
        /// <param name = "cmd">查询命令</param>
        /// <returns></returns>
        [EditorBrowsable(EditorBrowsableState.Never)]
        public DataTable ExecuteDataTable(SqlCommand cmd)
        {
            if (Connection == null)
                return null;

            cmd.Connection = Connection;
            try
            {
                var dt = new DataTable();
                using (IDataReader dr = CreateReader(cmd))
                    dt.Load(dr);
                return dt;
            }
            finally
            {
                if (!_keepOpen)
                    Connection.Close();
            }
        }

        /// <summary>
        ///   Descriptiont:将查询的第一个结果集作为DataTable返回
        ///   注意：性能较低的实现方式。不建议使用
        ///   Author:余观成
        ///   Date:2008/7/3
        /// </summary>
        /// <param name = "storedProcedure">存储过程</param>
        /// <param name = "parameters">参数数组</param>
        /// <returns></returns>
        [EditorBrowsable(EditorBrowsableState.Never)]
        public DataTable ExecuteDataTable(string storedProcedure, params SqlParameter[] parameters)
        {
            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection };
            if (parameters != null)
            {
                for (var i = 0; i < parameters.Length; i++)
                    cmd.Parameters.Add(parameters[i]);
            }
            return ExecuteDataTable(cmd);
        }

        /// <summary>
        ///   将查询的第一个结果集作为DataTable返回
        ///   注意：性能较低的实现方式。不建议使用
        /// </summary>
        /// <param name = "commandText">查询命令文本</param>
        /// <returns></returns>
        [EditorBrowsable(EditorBrowsableState.Never)]
        public DataTable ExecuteDataTable(string commandText)
        {
            return ExecuteDataTable(new SqlCommand(commandText));
        }

        /// <summary>
        ///   返回一个DataSet实例。
        ///   注意：性能较低的实现方式。不建议使用
        /// </summary>
        /// <param name = "cmd">查询命令</param>
        /// <returns></returns>
        [EditorBrowsable(EditorBrowsableState.Never)]
        public DataSet ExecuteDataSet(SqlCommand cmd)
        {
            if (Connection == null)
                return null;

            cmd.Connection = Connection;
            try
            {
                var da = new SqlDataAdapter(cmd);
                var ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            finally
            {
                if (!_keepOpen)
                    Connection.Close();
            }
        }

        /// <summary>
        ///   Descriptiont:将查询的第一个结果集作为DataSet返回
        ///   注意：性能较低的实现方式。不建议使用
        ///   Author:余观成
        ///   Date:2008/7/3
        /// </summary>
        /// <param name = "storedProcedure">存储过程</param>
        /// <param name = "parameters">参数数组</param>
        /// <returns></returns>
        [EditorBrowsable(EditorBrowsableState.Never)]
        public DataSet ExecuteDataSet(string storedProcedure, params SqlParameter[] parameters)
        {
            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = storedProcedure, Connection = Connection };
            if (parameters != null)
            {
                for (var i = 0; i < parameters.Length; i++)
                    cmd.Parameters.Add(parameters[i]);
            }
            return ExecuteDataSet(cmd);
        }

        /// <summary>
        ///   返回一个DataSet实例。
        ///   注意：性能较低的实现方式。不建议使用
        /// </summary>
        /// <param name = "commandText">SQL命令行</param>
        /// <returns></returns>
        [EditorBrowsable(EditorBrowsableState.Never)]
        public DataSet ExecuteDataSet(string commandText)
        {
            return ExecuteDataSet(new SqlCommand(commandText));
        }

        #endregion

        #region IDisposable 成员

        /// <summary>
        ///   释放资源。一般用于KeepOpen=true
        /// </summary>
        public void Dispose()
        {
            if (Connection != null)
                Connection.Dispose();
        }

        #endregion

        #region 其它转换函数

        /// <summary>
        ///   从SqlDbType到TypeCode的转换函数
        /// </summary>
        /// <param name = "sqlDbType"></param>
        /// <returns></returns>
        public static TypeCode SqlDBTypeToTypeCode(SqlDbType sqlDbType)
        {
            TypeCode typeCode;
            switch (sqlDbType)
            {
                case SqlDbType.BigInt:
                    typeCode = TypeCode.Int64;
                    break;
                case SqlDbType.Bit:
                    typeCode = TypeCode.Boolean;
                    break;
                case SqlDbType.Char:
                    typeCode = TypeCode.String;
                    break;
                case SqlDbType.DateTime:
                    typeCode = TypeCode.DateTime;
                    break;
                case SqlDbType.Decimal:
                    typeCode = TypeCode.Decimal;
                    break;
                case SqlDbType.Float:
                    typeCode = TypeCode.Double;
                    break;
                case SqlDbType.Int:
                    typeCode = TypeCode.Int32;
                    break;
                case SqlDbType.Money:
                    typeCode = TypeCode.Decimal;
                    break;
                case SqlDbType.NChar:
                    typeCode = TypeCode.String;
                    break;
                case SqlDbType.NText:
                    typeCode = TypeCode.String;
                    break;
                case SqlDbType.NVarChar:
                    typeCode = TypeCode.String;
                    break;
                case SqlDbType.Real:
                    typeCode = TypeCode.Single;
                    break;
                case SqlDbType.SmallDateTime:
                    typeCode = TypeCode.DateTime;
                    break;
                case SqlDbType.SmallInt:
                    typeCode = TypeCode.Int16;
                    break;
                case SqlDbType.SmallMoney:
                    typeCode = TypeCode.Decimal;
                    break;
                case SqlDbType.Text:
                    typeCode = TypeCode.String;
                    break;
                case SqlDbType.TinyInt:
                    typeCode = TypeCode.SByte;
                    break;
                case SqlDbType.VarChar:
                    typeCode = TypeCode.Single;
                    break;
                default:
                    typeCode = TypeCode.Object;
                    break;
            }
            return typeCode;
        }

        #endregion

        /// <summary>
        ///   将传入参数尝试转化为指定类型。转换失败返回空
        /// </summary>
        /// <typeparam name = "T"></typeparam>
        /// <param name = "obj"></param>
        /// <returns></returns>
        public static T? GetNullableValue<T>(object obj) where T : struct
        {
            return obj is T ? (T)obj : new T?();
        }

        /// <summary>
        ///   返回一个执行存储过程的SqlCommand实例
        /// </summary>
        /// <param name = "name"></param>
        /// <returns></returns>
        public static SqlCommand CreateSpCommand(string name)
        {
            return new SqlCommand(name) { CommandType = CommandType.StoredProcedure };
        }
    }
}